package utils;


import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

import org.w3c.dom.Node;

import componentManager.Doc;
import componentManager.Noeud;
import componentManager.NoeudResult;
import componentManager.NoeudTerme;
import componentManager.Terme;


public class BDD {
	
    private static BDD single;
    private static Connection connect;
    private static String url = "jdbc:mysql://localhost:3306/repco2";
    private static String user = "root";
    private static String passwd = "";
	
    private BDD() {
        try {
            connect = DriverManager.getConnection(url, user, passwd);
            System.out.println("Connection effective !");
        } catch (SQLException e) {
            System.out.println("Erreur connection"+e.toString());
        }
    }
    
    public static BDD getInstance(){
        synchronized (BDD.class) {
            if (single == null) {
                single = new BDD();
            }
        }
        return single;
    }

    /**
     * Ajoute un élément dans la BDD
     * @param label : nom de la balise
     * @param idDoc : id du document auquel appartient l'élément à ajouter
     * @return true si l'ajout réussi et false sinon
     */
    public static boolean ajouterNoeud(Noeud n){
        int result=0;
         try {
            String query = "Insert into element values(?,?,?,?,?)";
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            stm.setInt(1, n.getId());
            stm.setString(2, n.getLabel());
            stm.setString(3, n.getPath());
            stm.setInt(4, n.getIdDoc());
            stm.setInt(5, n.getIdNoeudpere());
            result = stm.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Erreur ajouterNoeud: "+e.toString());
        }
        return (result>0);
    }
    
    public static boolean ajouterTousLesNoeud(ArrayList<Noeud> listeNoeuds){
        String query = "Insert into element values";
        for(int i=0;i<listeNoeuds.size();i++){
        	query += "(?,?,?,?,?),";  
        }
        query = query.substring(0, query.length()-1);
    	
    	int result=0;
         try {
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            int i=1;
            for(Noeud n : listeNoeuds){
            	stm.setInt(i++, n.getId());
                stm.setString(i++, n.getLabel());
                stm.setString(i++, n.getPath());
                stm.setInt(i++, n.getIdDoc());
                stm.setInt(i++, n.getIdNoeudpere());
            }
            
            result = stm.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Erreur ajouterTousLesNoeud: "+e.toString());
        }
        return (result>0);
    }
    
    /**
     * Ajoute un terme dans la BDD
     * @param value le terme apres lemmatisation
     * @return true si l'ajout réussi et false sinon
     */
    public static boolean ajouterTerme(Terme t){
        int result=0;
        try {
            String query = "Insert into terme values(?,?)";
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            stm.setString(1, t.getRadical());
            result = stm.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Erreur ajouterTerme: "+e.toString());
        }
        return (result>0);
    }
    
    public static boolean ajouterTousLesTerme(ArrayList<Terme> listeTerme){
   	 String query = "Insert into terme values";
        for(int i=0;i<listeTerme.size();i++){
        	query += "(?,?),";  
        }
        query = query.substring(0, query.length()-1);
    	
       int result=0;
       try {
           PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
           int i=1;
           for(Terme t: listeTerme){
        	 stm.setInt(i++, t.getId());
           	stm.setString(i++, t.getRadical());
           }
           result = stm.executeUpdate();
       } catch (SQLException e) {
           System.out.println("Erreur ajouterTousLesTerme: "+e.toString());
       }
       return (result>0);
   }
    
    /**
     * Ajoute un terme, l'élément auxquel il appartient et la position du terme dans la BDD
     * @param idElement : id de l'élément
     * @param idTerme : id du terme
     * @param position : postion du terme dans le texte de  l'élément
     * @return true si l'ajout réussi et false sinon
     */
    public static boolean ajouterElementTerme(int idElement,int idTerme,int position){
        int result=0;
        try {
            String query = "Insert into elementterme values(?,?,?)";
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            stm.setInt(1,idElement);
            stm.setInt(2,idTerme);
            stm.setInt(3,position);
            result = stm.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Erreur ajouterElementTerme : "+e.toString());
        }
        return (result>0);
    }
    
    public static boolean ajouterTousLesElementTerme(ArrayList<NoeudTerme> listeTerme){
    	int result=0;
        String query = "Insert into elementterme values";
        for(int i=0;i<listeTerme.size();i++){
        	query += "(?,?,?),";  
        }
        query = query.substring(0, query.length()-1);
        
        try {
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            int i=1;
            for(NoeudTerme t : listeTerme){
            	stm.setInt(i++,t.getIdNoeud());
        		stm.setInt(i++,t.getIdTerme());
        		stm.setInt(i++,t.getPosition());
            }
            result = stm.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Erreur ajouterTousLesElementTerme : "+e.toString());
        }
        return (result>0);
    }
    	
    /**
     * Ajoute un document dans la BDD
     * @return true si l'ajout réussi et false sinon
     */
    public static boolean ajouterDocument(Doc d){
        int result=0;
        try {
            String query = "Insert into document values(?,?,?)";
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            stm.setInt(1,d.getId());
            stm.setInt(2,d.getNbreTermes());
            stm.setString(3,d.getNomDoc());
            result = stm.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Erreur ajouterDocument: "+e.toString());
        }
        return (result>0);
    }
    public static boolean ajouterTousLesDocument(ArrayList<Doc> listDoc){
        int result=0;
        String query = "Insert into document values ";
        for(int i=0;i<listDoc.size();i++){
        	query += "(?,?,?),";  
        }
        query = query.substring(0, query.length()-1);
        try {
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
            int i=1;
            for(Doc d: listDoc){
            	stm.setInt(i++,d.getId());
            	stm.setInt(i++,d.getNbreTermes());
            	stm.setString(i++,d.getNomDoc());
            }
            result = stm.executeUpdate();
        } catch (SQLException e) {
            System.out.println("Erreur ajouterTousLesDocument: "+e.toString());
        }
        return (result>0);
    }
    
    //<nomDocument,liste des noeuds contenant le terme>
    public static HashMap<String,ArrayList<NoeudResult>> getOccurencesOfTerme(String radical){
//    	ArrayList<NoeudTerme> result= new ArrayList<NoeudTerme>();
    	HashMap<String,ArrayList<NoeudResult>> result = new HashMap<String, ArrayList<NoeudResult>>();
    	NoeudResult n = null;
    	Doc doc = null;
        try {
            String query ="SELECT D.nomDoc,D.idDoc,D.NbreTerm, E.idElem,count(T.idTerme) as Freq, E.label,E.path FROM elementterme ET," +
            		" document D,element E, terme T where T.radical=? and T.idTerme = ET.idTErme" +
            		" and ET.idElem=E.idElem and E.idDoc=D.idDoc group by E.idElem";
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stm.setString(1, radical);
            ResultSet res = stm.executeQuery();
            while(res.next()){
            	int i=1;
            	String nomDoc = res.getString(i++);
            	doc = new Doc(res.getInt(i++),res.getInt(i++),nomDoc);
            	n = new NoeudResult();
            	n.setDoc(doc);
            	n.setIdElem(res.getInt(i++));
            	n.setFreq(res.getInt(i++));
            	n.setLabel(res.getString(i++));
            	n.setPath(res.getString(i++));
            	if(!result.containsKey(nomDoc)){
            		ArrayList<NoeudResult> a = new ArrayList<NoeudResult>();
            		a.add(n);
            		result.put(nomDoc,a);
            	}else{
            		result.get(nomDoc).add(n);
            	}
            }
        } catch (SQLException e) {
            System.out.println("Erreur getOccurencesOfTerme"+e.toString());
        }
        return result;
    }
    
    public static HashMap<String,ArrayList<NoeudResult>> getOccurencesOfExpression(String radical){
    	int nbreMots = radical.split(Parser.DELIMITERS).length;
//    	ArrayList<NoeudTerme> result= new ArrayList<NoeudTerme>();
    	HashMap<String,ArrayList<NoeudResult>> result = new HashMap<String, ArrayList<NoeudResult>>();
    	NoeudResult n = null;
    	Doc doc = null;
        try {
            String query ="SELECT D.nomDoc,D.idDoc,D.NbreTerm, E.idElem,count(T.idTerme) as Freq, E.label,E.path FROM elementterme ET," +
            		" document D,element E, terme T where T.radical=? and T.idTerme = ET.idTErme" +
            		" and ET.idElem=E.idElem and E.idDoc=D.idDoc group by E.idElem";
            
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stm.setString(1, radical);
            ResultSet res = stm.executeQuery();
            while(res.next()){
            	int i=1;
            	String nomDoc = res.getString(i++);
            	doc = new Doc(res.getInt(i++),res.getInt(i++),nomDoc);
            	n = new NoeudResult();
            	n.setDoc(doc);
            	n.setIdElem(res.getInt(i++));
            	n.setFreq(res.getInt(i++));
            	n.setLabel(res.getString(i++));
            	n.setPath(res.getString(i++));
            	if(!result.containsKey(nomDoc)){
            		ArrayList<NoeudResult> a = new ArrayList<NoeudResult>();
            		a.add(n);
            		result.put(nomDoc,a);
            	}else{
            		result.get(nomDoc).add(n);
            	}
            }
        } catch (SQLException e) {
            System.out.println("Erreur getOccurencesOfTerme"+e.toString());
        }
        return result;
    }
    
    /**
     * Retourne la liste des id des documents dans lesquels le terme specifi� apparait
     * @param radical
     * @return
     */
    public static ArrayList<Integer> getIdDocsByTerme(String radical){
    	ArrayList<Integer> result= new ArrayList<Integer>();
        try {
            String query ="SELECT D.idDoc FROM elementterme ET," +
            		" document D,element E, terme T where T.radical=? and T.idTerme = ET.idTErme" +
            		" and ET.idElem=E.idElem and E.idDoc=D.idDoc";
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
            stm.setString(1, radical);
            ResultSet res = stm.executeQuery();
            while(res.next()){
            	int t = res.getInt(1);
            	if(!result.contains(t))
            		result.add(t);
            }
        } catch (SQLException e) {
            System.out.println("Erreur getOccurencesOfTerme"+e.toString());
        }
        return result;
    }
    
    /**
     * Retourne le nombre de documents de la collection
     * @return
     */
    public static int getDocsNumber(){
    	int nbreDoc = -1;
    	try {
            String query ="select count(*)as id from document";
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
            ResultSet res = stm.executeQuery();
            res.first();
            nbreDoc = res.getInt(1);
        } catch (SQLException e) {
            System.out.println("Erreur getDocsNumber"+e.toString());
        }
    	return nbreDoc;
    }
    
    /**
     * Retourne le nombre total de termes de tous les documents
     * @return
     */
    public static int getTermsNumber(){
    	int nbreTermes = -1;
    	try {
            String query ="select sum(NbreTerm) as nbre from document";
            PreparedStatement stm = connect.prepareStatement(query,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
            ResultSet res = stm.executeQuery();
            res.first();
            nbreTermes = res.getInt(1);
        } catch (SQLException e) {
            System.out.println("Erreur getTermsNumber"+e.toString());
        }
    	return nbreTermes;
    }
    
   
    
    
    //TODO: 
    /*
     * NoeudResult{score(freq),path,Node}
     * <idDoc,ArrayList<NoeudResult> getOcuurence pour plusieurs terme entre guillemets(donc prise en compte de la position)
     * getLongueurDoc(id)
     * 
     * pour tous les NR
     * tq pere!null
     * recupere pere
     * si le pere n'est pas dans la liste des NR (clé = Node) on le cree et on lui met le sore du fils et le Node
     * sinon on met à jour le score
     * a la sorti ona une evaluation du terme dans le doc 
     * 
    */
//     public static void main(String args[]){
//    	BDD.getInstance();
//        System.out.println(BDD.getOccurencesOfTerme(Terme.lemmatise("contexte")));
//        //longueur moyenne d'un doc = nbre de termes / nbre de docs
////    	System.out.println(BDD.getTermsNumber()/BDD.getDocsNumber());
//        System.out.println(BDD.getIdDocsByTerme(Terme.lemmatise("contexte")));
//    }

}
